USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetTasks')
	BEGIN
		DROP  Procedure  dbo.GetTasks
	END

GO

CREATE Procedure dbo.GetTasks
	@userId INT
AS
BEGIN

	;WITH A AS (
		SELECT 
			a.ActivityTaskID	AS TaskID
		,	a.ActivityTaskGUID	AS PKID
		,	a.[Description]		AS TaskName
		,	s.StatusDesc		as ActivityStatus
		,	a.StartDate			as StartDate
		,	a.EndDate			as EndDate
		FROM TR_ACTIVITYTASK as a WITH (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			on r.SourceGUID = a.ActivityTaskGUID
		LEFT OUTER JOIN TR_STATUS as s WITH (NOLOCK)
			on s.Status = a.Status
		WHERE 
			r.Partner2 = @userID
		AND r.RelationShipType = 3 -- OWNER
		AND	ISNULL(a.IsTask,0) = 1
	), B AS (
		-- GET ACCOUNTS FOR THE TASKS
		SELECT 
			act.PartnerName		as 	AccountName
		,	a.TaskID
		FROM A 
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			ON r.SourceGUID = a.PKID
		INNER JOIN TR_PARTNER as act WITH (NOLOCK)
			on act.PartnerNo = r.Partner2
		WHERE
			r.RelationShiptype = 10 -- 10: Task Partner
	), C AS (
		-- GET CONTACT FOR THE ACTIVITIES
		SELECT 
			(con.PartnerFirstName + ' ' + con.PartnerLastName) as  ContactName
		,	a.TaskID
		FROM A 
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			ON r.SourceGUID = a.PKID
		INNER JOIN TR_PARTNER as con WITH (NOLOCK)
			on con.PartnerNo = r.Partner2
		WHERE
			r.RelationShiptype = 2 -- 2: Contact
			AND r.IsMain = 1
	), Final AS (
		SELECT
			a.TaskID			AS [Task ID]
		,	a.TaskName			AS [Task Name]
		,	b.AccountName		AS [Account Name]
		,	a.ActivityStatus	as [Status]
		,	CONVERT(VARCHAR(10), a.StartDate, 101)			as [Start Date]
		,	c.ContactName		as [Contact]
		,	a.PKID				AS [PKID]
		FROM A
		LEFT OUTER JOIN B
			on B.TaskID = A.TaskID
		LEFT OUTER JOIN C
			on C.TaskID = A.TaskID
	)

	SELECT * FROM Final

END
GO

-- exec GETTasks 1